<?php
/**
 * Pert Library
 * SQL Generator Classes
 *
 * @author	Emre Hasegeli <hasegeli@gmail.com>
 * @license	GNU General Public License v3
 */

namespace Pert\SQLGenerator;

class Input
{
	private $id;

	public function __Construct($id)
	{
		$this->id = $id;
	}

	public function SQL()
	{
		if(is_array($this->id))
			return new ArrayInput($this->id);

		if(is_bool($this->id))
			if($this->id)
				return 'true';
			else
				return 'false';

		if($this->id instanceof \DateTime)
			return "'" . $this->id->format('Y-m-d') . "'";

		if(is_int($this->id))
			return $this->id;

		if(is_float($this->id))
			return $this->id;

		if(!$this->id)
			return 'NULL';

		$id = $this->id;

		if(get_magic_quotes_gpc())
			$id = stripslashes($id);

		$id = pg_escape_string($id);

		return "'" . $id . "'";
	}

	public function StringSQL()
	{
		$id = trim($this->id);

		if(get_magic_quotes_gpc())
			$id = stripslashes($id);

		$id = pg_escape_string($id);

		return "'" . $id . "'";
	}

	public function LikeStringSQL()
	{
		$id = trim($this->id);

		if(get_magic_quotes_gpc())
			$id = stripslashes($id);

		$id = pg_escape_string($id);

		return "'%" . $id . "%'";
	}
}

class ArrayInput
{
	private $array;

	public function __Construct(Array $array)
	{
		$this->array = $array;
	}

	public function SQL()
	{
		$sQL = '(';

		$count = 0;

		foreach($this->array AS $input)
		{
			if($count++)
				$sQL .= ', ';

			$input = new Input($input);

			$sQL .= $input->SQL();
		}

		return $sQL . ')';
	}
}

abstract class aCondition
{
	protected $column;

	public function __Construct($column)
	{
		$this->column = $column;
	}
}

class NullCondition extends aCondition
{
	public function SQL($table)
	{
		return '"' . $table . '"."' . $this->column . '" IS NULL';
	}
}

class Condition extends aCondition
{
	protected $input;

	public function __Construct($column,
			Input $input)
	{
		parent::__Construct($column);

		$this->input = $input;
	}

	public function SQL($table)
	{
		return '"' . $table . '"."' . $this->column . '" = ' . $this->input->SQL();
	}
}

class StringCondition extends Condition
{
	public function SQL($table)
	{
		return '"' . $table . '"."' . $this->column . '" = ' . $this->input->StringSQL();
	}
}

class OppositeStringCondition extends Condition
{
	public function SQL($table)
	{
		return '"' . $table . '"."' . $this->column . '" != ' . $this->input->StringSQL();
	}
}

class LikeStringCondition extends StringCondition
{
	public function SQL($table)
	{
		return '"' . $table . '"."' . $this->column . '" ~~* ' . $this->input->LikeStringSQL();
	}
}

class ArrayCondition extends aCondition
{
	protected $input;

	public function __Construct($column,
			ArrayInput $input)
	{
		parent::__Construct($column,
				$input);

		$this->input = $input;
	}

	public function SQL($table)
	{
		return '"' . $table . '"."' . $this->column . '" IN ' . $this->input->SQL();
	}
}

class Table
{
	private $name;

	private $schema;

	private $columnArray;

	public function __Construct($name,
			$schema,
			Array $columnArray)
	{
		$this->name = $name;

		$this->schema = $schema;

		$this->columnArray = $columnArray;
	}

	final public function __ToString()
	{
		$nameArray = explode('_',
				$this->name);

		$name = '';

		$id = 0;

		while(((isset($nameArray[$id]))) AND (!is_numeric($nameArray[$id])))
		{
			if(ucfirst($nameArray[$id]) == $nameArray[$id])
				$name = $nameArray[$id];
			else
				$name .= ucfirst($nameArray[$id]);

			$id++;
		}

		return $name;
	}

	final public function Schema()
	{
		return $this->schema;
	}

	final public function NameColumn()
	{
		return $this->columnArray[1];
	}

	final public function UnpublishedColumn()
	{
		if(isset($this->columnArray[2]) AND ($this->columnArray[2] == 'Unpublished'))
			return $this->columnArray[2];
	}

	private $parent;

	private $childArray = array();

	final public function AddChild(self $child)
	{
		$child->parent = $this;

		$this->childArray[] = $child;

		return $child;
	}

	final public function GetParent()
	{
		if(isset($this->parent))
			return $this->parent;
	}

	private $conditionArray = array();

	final public function AddCondition(aCondition $condition)
	{
		$this->conditionArray[] = $condition;
	}

	protected function selectClauseNameCoreSQL($prefix = '')
	{
		if($this->NameColumn())
			return '"' . $prefix . $this . '"."' . $this->NameColumn() . '"::text';
	}

	final protected function selectClauseNameSQL($prefix = '')
	{
		$sQL = '';

		if(isset($this->parent)) 
			$sQL .= $this->parent->selectClauseNameSQL($prefix);

		if($selectClauseNameCoreSQL = $this->selectClauseNameCoreSQL($prefix))
		{
			if($sQL)
				$sQL .= " || ' ' || ";

			$sQL .= $selectClauseNameCoreSQL;
		}

		return $sQL;
	}

	protected function selectClausePublishedSQL($prefix = '')
	{
		$sQL = '';

		if($this->UnpublishedColumn())
			$sQL .= '"' . $prefix . $this . '"."Unpublished" = false';

		if(isset($this->parent) AND ($selectClausePublishedSQL = $this->parent->selectClausePublishedSQL($prefix)))
		{
			if($sQL)
				$sQL = '
AND ';

			$sQL .= $selectClausePublishedSQL;
		}

		return $sQL;
	}

	final protected function selectClauseSQL()
	{
		$sQL = '"' . $this . '".id';

		if(($selectClauseNameSQL = $this->selectClauseNameSQL()))
			$sQL .= ',
		' . $selectClauseNameSQL . ' "Name"';

		if(($selectClausePublishedSQL = $this->selectClausePublishedSQL()))
			$sQL .= ',
		(' . $selectClausePublishedSQL . ') "Published"';

		return $sQL;
	}

	protected function fromClauseCoreSQL($prefix = '')
	{
		$sQL = '"' . $this->Schema() . '"."' . $this->name . '"';

		if(($prefix) OR ($this != $this->name))
			$sQL .= ' "' . $prefix . $this . '"';

		return $sQL;
	}

	final protected function fromClauseJoinAsChildSQL($prefix = '')
	{
		$sQL = $this->fromClauseCoreSQL($prefix);

		foreach($this->childArray AS $child)
			$sQL .= '
JOIN ' . $child->fromClauseJoinAsChildSQL($prefix) . ' ON "' . $prefix . $child . '"."' . $this . '" = "' . $prefix . $this . '".id';

		return $sQL;
	}

	final protected function fromClauseJoinAsParentSQL(self $joined,
			$prefix = '')
	{
		$sQL = $this->fromClauseCoreSQL($prefix);

		if(isset($this->parent))
			$sQL .= '
JOIN ' . $this->parent->fromClauseJoinAsParentSQL($this,
		$prefix) . ' ON "' . $prefix . $this->parent . '".id = "' . $prefix . $this . '"."' . $this->parent . '"';

		foreach($this->childArray AS $child)
			if($child != $joined)
				$sQL .= '
JOIN ' . $child->fromClauseJoinAsChildSQL($prefix) . ' ON "' . $prefix . $child . '"."' . $this . '" = "' . $prefix . $this . '".id';

		return $sQL;
	}

	final protected function fromClauseSQL($prefix = '')
	{
		$sQL = $this->fromClauseCoreSQL($prefix);

		if(isset($this->parent))
			$sQL .= '
JOIN ' . $this->parent->fromClauseJoinAsParentSQL($this,
		'') . ' ON "' . $prefix . $this->parent . '".id = "' . $prefix . $this . '"."' . $this->parent . '"';

		foreach($this->childArray AS $child)
			$sQL .= '
JOIN ' . $child->fromClauseJoinAsChildSQL($prefix) . ' ON "' . $prefix . $child . '"."' . $this . '" = "' . $prefix . $this . '".id';

		return $sQL;
	}

	protected function whereClauseCoreSQL($prefix = '')
	{
		$sQL = '';

		foreach($this->conditionArray AS $condition)
		{
			if($sQL)
				$sQL .= '
AND ';

			$sQL .= $condition->SQL($prefix . $this);
		}

		return $sQL;
	}

	final protected function whereClauseJoinAsChildSQL($prefix = '')
	{
		$sQL = $this->whereClauseCoreSQL($prefix);

		foreach($this->childArray AS $child)
			if($whereClauseJoinAsChildSQL = $child->whereClauseJoinAsChildSQL($prefix))
			{
				if($sQL)
					$sQL .= '
AND ';

				$sQL .= $whereClauseJoinAsChildSQL;
			}

		return $sQL;
	}

	final protected function whereClauseJoinAsParentSQL(self $joined,
			$prefix = '')
	{
		$sQL = $this->whereClauseCoreSQL($prefix);

		if(isset($this->parent))
			if($whereClauseJoinAsParentSQL = $this->parent->whereClauseJoinAsParentSQL($this,
					$prefix))
			{
				if($sQL)
					$sQL .= '
AND ';

				$sQL .= $whereClauseJoinAsParentSQL;
			}

		foreach($this->childArray AS $child)
			if($child != $joined)
				if($whereClauseJoinAsChildSQL = $child->whereClauseJoinAsChildSQL($prefix))
				{
					if($sQL)
						$sQL .= '
AND ';

					$sQL .= $whereClauseJoinAsChildSQL;
				}

		return $sQL;
	}

	final protected function whereClauseSQL($prefix = '')
	{
		$sQL = '';

		if(isset($this->parent))
			$sQL .= $this->parent->whereClauseJoinAsParentSQL($this,
					$prefix);

		foreach($this->conditionArray AS $condition)
		{
			if($sQL)
				$sQL .= '
AND ';

			$sQL .= $condition->SQL($this);
		}

		foreach($this->childArray AS $child)
			if($whereClauseJoinAsChildSQL = $child->whereClauseJoinAsChildSQL($prefix))
			{
				if($sQL)
					$sQL .= '
AND ';
	
				$sQL .= $whereClauseJoinAsChildSQL;
			}

		return $sQL;
	}

	final public function SelectSQL()
	{
		$sQL = '
SELECT ' . $this->selectClauseSQL() . '
	FROM ' . $this->fromClauseSQL();

		if($whereClauseSQL = $this->whereClauseSQL())
			$sQL .= '
	WHERE ' . $whereClauseSQL;

		$sQL .= '
	ORDER BY "Name"';

		return $sQL;
	}
}

class LingualNamedTable extends Table
{
	private $privilegedLanguageArray;

	private $alternativeName;

	final public function __Construct($name,
			$schema,
			Array $columnArray,
			Array $privilegedLanguageArray,
			$alternativeName)
	{
		parent::__Construct($name,
				$schema,
				$columnArray);

		$this->privilegedLanguageArray = $privilegedLanguageArray;

		$this->alternativeName = $alternativeName;
	}

	final protected function selectClauseNameCoreSQL($prefix = '')
	{
		return '"' . $prefix . $this . '_name"."Name"';
	}

	final protected function fromClauseCoreSQL($prefix = '')
	{
		$privilegedLanguageArrayInput = new ArrayInput($this->privilegedLanguageArray);

		$sQL = parent::fromClauseCoreSQL($prefix) . '
JOIN "' . $this->Schema() . '"."' . $this . '_name" "' . $prefix . $this . '_name" ON "' . $prefix . $this . '_name"."' . $this . '" = "' . $prefix . $this . '".id
		AND "' . $prefix . $this . '_name"."Language" IN ' . $privilegedLanguageArrayInput->SQL();

		if($this->alternativeName)
			$sQL .= '
		AND "' . $prefix . $this . '_name"."Alternative" = false';

		return $sQL;
	}
}

class IndicativeTable extends Table
{
	private $indicated;

	public function __Construct($name,
			$schema,
			Array $columnArray,
			$indicated)
	{
		parent::__Construct($name,
				$schema,
				$columnArray);

		$this->indicated = $indicated;
	}

	protected function selectClauseNameCoreSQL($prefix = '')
	{
		return $this->indicated->selectClauseNameSQL('Indicated_' . $prefix);
	}

	protected function selectClausePublishedSQL($prefix = '')
	{
		$sQL = parent::selectClausePublishedSQL($prefix);

		if($unpublishedWhereClauseSQL = $this->indicated->selectClausePublishedSQL('Indicated_' . $prefix))
		{
			if($sQL)
				$sQL = '
AND ';
			$sQL .= $unpublishedWhereClauseSQL;
		}

		return $sQL;
	}

	protected function fromClauseCoreSQL($prefix = '')
	{
		return parent::fromClauseCoreSQL($prefix) . '
JOIN ' . $this->indicated->fromClauseSQL('Indicated_' . $prefix) . ' ON "Indicated_' . $prefix . $this->indicated . '".id = "' . $prefix . $this . '"."' . $this->NameColumn(). '"';
	}

	protected function whereClauseCoreSQL($prefix = '')
	{
		$sQL = parent::whereClauseCoreSQL($prefix);

		if($whereClauseSQL = $this->indicated->whereClauseSQL('Indicated_' . $prefix))
		{
			if($sQL)
				$sQL .= '
AND ' . $whereClauseSQL;
		}

		return $sQL;
	}
}
